﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace ResultsManagementSystem
{
    public class ResultDao
    {
        private Result result;
        private SqlCeConnection conn;
        private SqlCeCommand cmd;
        private SqlCeDataReader rdr;

        public ResultDao()
        {
            this.result = new Result();

            conn = new SqlCeConnection(@"Data Source=../../Results.sdf");
            conn.Open();

            cmd = conn.CreateCommand();
        }

        ~ResultDao()
        {
            conn.Close();
        }

        public Result getResult()
        {
            return this.result;
        }

        public void setResult(Result result)
        {
            this.result = result;
        }

        //查询所有同学
        public List<Result> queryAllResult()
        {
            List<Result> resultList = new List<Result>();

            cmd.CommandText = "SELECT * FROM Result";

            rdr = cmd.ExecuteReader();

            while(rdr.Read())
            {
                resultList.Add(new Result(rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2), rdr.GetInt32(3), rdr.GetInt32(4), rdr.GetInt32(5), rdr.GetInt32(6)));
            }

            return resultList;
        }

        //查询所有平均分不低于85同学
        public List<Result> queryMoreThen85Result()
        {
            List<Result> resultList = new List<Result>();

            cmd.CommandText = "SELECT * FROM Result where allMoreThen85 = 1";

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                resultList.Add(new Result(rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2), rdr.GetInt32(3), rdr.GetInt32(4), rdr.GetInt32(5), rdr.GetInt32(6)));
            }

            return resultList;
        }

        //查询某一个同学
        public Result queryResult(String number)
        {
            Result result = new Result();

            cmd.CommandText = "SELECT * FROM Result where number = '" +number+ "'";

            rdr = cmd.ExecuteReader();

            if (rdr.Read())
            {
                result = new Result(rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2), rdr.GetInt32(3), rdr.GetInt32(4), rdr.GetInt32(5), rdr.GetInt32(6));
            }
            else
            {
                result = null;
            }
            return result;
        }

        public bool insertResult()
        {
            //得到所有课程
            SubjectDao subjectDao = new SubjectDao();

            List<Subject> subjectList = subjectDao.queryAllSubject();

            Subject subject = new Subject();

            //学生统计结果
            StudentDao studentDao = new StudentDao();

            List<Student> studentList = studentDao.queryAllStudent();

            List<Result> resultList = new List<Result>();

            for (int i = 0; i < studentList.Count;i++ )
            {
                resultList.Add(new Result(studentList.ElementAt(i).getNumber()));
            }

            //遍历所有课程所有人的成绩
            for (int i = 0; i < subjectList.Count;i++ )
            {
                subject = subjectList.ElementAt(i);

                ScoreDao scoreDao = new ScoreDao();

                List<Score> scoreList = scoreDao.querryScoreListBySubjectName(subject.getName());

                Score score = new Score();

                //遍历某一个课程学生的成绩
                for (int j = 0; j < scoreList.Count;j++ )
                {
                    score = scoreList.ElementAt(j);

                    Result result = new Result();

                    for (int k = 0; k < resultList.Count; k++ )
                    {
                        result = resultList.ElementAt(k);

                        //找到匹配的同学
                        if(result.getNumber() == score.getNumber())
                        {
                            //判断最高成绩
                            if (result.getMax() < score.getScore())
                            {
                                resultList.ElementAt(k).setMax(score.getScore());
                            }

                            //判断最低成绩
                            if(result.getMin() > score.getScore())
                            {
                                resultList.ElementAt(k).setMin(score.getScore());
                            }

                            //判断是否成绩都不低于于85
                            if(score.getScore() < 85)
                            {
                                resultList.ElementAt(k).setAllMoreThen85(0);
                            }

                            //计算平均成绩
                            resultList.ElementAt(k).setAve((resultList.ElementAt(k).getAve() * resultList.ElementAt(k).getSum() + score.getScore()) / (resultList.ElementAt(k).getSum() + 1));

                            //增加课程数
                            resultList.ElementAt(k).setSum(resultList.ElementAt(k).getSum() + 1);
                        }
                    }
                }
            }

            //设定没有任何成绩的同学
            for (int h = 0; h < resultList.Count; h++)
            {
                Result result = resultList.ElementAt(h);

                if(result.getSum() == 0)
                {
                    resultList.ElementAt(h).setAve(0);
                    resultList.ElementAt(h).setAllMoreThen85(0);
                    resultList.ElementAt(h).setMax(0);
                    resultList.ElementAt(h).setMin(0);
                }
            }

            //将数据插入数据库
            for (int l = 0; l < resultList.Count;l++ )
            {
                Result result = resultList.ElementAt(l);

                //判断数据是否存在，不存在插入
                if (queryResult(result.getNumber()) == null)
                {
                    //插入数据
                    cmd.CommandText = "insert into Result(number,max,min,ave,allMoreThen85,sum) values('" + result.getNumber() + "','" + result.getMax() + "','" + result.getMin() + "','" + result.getAve() + "','" + result.getAllMoreThen85() + "','" + result.getSum() + "')";
                    cmd.ExecuteNonQuery();
                }
            }

            return true;
        }
    }
}
